package com.database.dao.impl;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.database.bean.CompanyRoundDecisionAndResult;
import com.database.bean.Game;
import com.database.bean.Round;
import com.database.bean.User;
import com.math.util.StatisticBean;

@Named("statisticDao")
@Component
public class StatisticDaoImpl implements Serializable{
	
	// getMarketRound -- zeigt alle Unternehmensdaten einer Runde an
	// getMarketGame -- zeigt alle Unternehmensdaten aller Runden an
	// getCompanyRound -- zeigt alle Firmendaten einer Runde an
	// getCompanyGame -- zeigt alle Firmendaten aller Runden an
	
	private static final long serialVersionUID = 1L;
	protected Logger logger = LoggerFactory.getLogger(getClass());

	
	private SessionFactory sessionFactory;

	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}
	
	@Autowired
	public void setSessionFactory(SessionFactory sessionFactory) {
		logger.debug("Set Autowired SessionFactory: {}",sessionFactory);
		this.sessionFactory = sessionFactory;
	}
	
	/**
	 * @param queryClass
	 */
	public StatisticDaoImpl() {
		super();
	}

	//-------------------------------Markus------------------------------------
	
	public List<StatisticBean> getMarketRoundSellings(Round round) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		
		Session session = sessionFactory.openSession();

		/*
		select companyproduct_id,p.sellingprice,count(*) from agent a,companyproduct p where p.id=a.companyproduct_id group by companyproduct_id order by count(*) desc;
		*/
		
		session.close();
		
		return result;
	}
	
	
	/**
	 * Method to read the top selling companyproducts with their information per game
	 * @param game
	 * @return
	 */
	public List<StatisticBean> getMarketGameAgenttypeAmount(Game game) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select r.roundNumber,at.amount, at.agentName from "
					+ "agenttype at, round r "
					+ "where at.roundid=r.id and r.gameid= :gameid "
					+ "order by r.roundNumber asc";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", game.getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setAgType_amount(((Integer)row.get("amount")));
				statBean.setAgType_agentName((String)row.get("agentName"));
				statBean.setRound_number(((Integer)row.get("roundNumber")));
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	
	/**
	 * Method to read the top selling companyproducts with their information per game
	 * @param game
	 * @return
	 */
	public List<StatisticBean> getMarketGameSellings(Game game) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select u.nickname,p.productname,cp.sellingprice as 'price',count(*) as 'anz' from "
					+ "agent a, agenttype at, round r,"
					+ "companyproduct cp,product p, companyrounddecisionandresult crd, user u "
					+ "where cp.id=a.companyproduct_id and a.agenttype_id=at.id "
					+ "and at.roundid=r.id and r.gameid= :gameid and cp.productid=p.id and cp.decresid=crd.id and "
					+ "u.id=crd.companyid "
					+ "group by companyproduct_id order by count(*) desc";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", game.getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setCompprod_sellingPrice((Double)row.get("price"));
				statBean.setCompprod_amountSold(((BigInteger)row.get("anz")));
				statBean.setProduct_name(((String)row.get("productname")));
				statBean.setCompany_nickname(((String)row.get("nickname")));
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	
	/**
	 * method to read the relevant company values from each company per game
	 * @param game
	 * @return
	 */
	public List<StatisticBean> getMarketGameFirmData(Game game) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select u.imageValue,u.budget,u.nickname from user u where u.gameid = :gameid";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", game.getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setCompany_budget((Double)row.get("budget"));
				statBean.setCompany_imageValue((Double)row.get("imageValue"));
				statBean.setCompany_nickname((String)row.get("nickname"));
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}

	
	/**
	 * Method to read all the relevant data for each round decision per company per game
	 * @param game
	 * @return
	 */
	public List<StatisticBean> getMarketGameRoundDecisionData(Game game) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select rep.reportTypeName, crd.newBudget, crd.reportCosts,crd.newImage,crd.revenue,crd.expenses,crd.overdraftCosts,crd.logisticCosts,crd.marketingInvestment,u.nickname,u.budget,crd.employerEducationInvestment,crd.rdInvestment,r.roundNumber "
					+ "from COMPANYROUNDDECISIONANDRESULT crd LEFT JOIN REPORT_DECISION red on (red.decres_Id=crd.id) LEFT JOIN REPORT rep on (rep.id=red.report_id)"
					+ ",round r,"
					+ "user u "
					+ "where crd.roundid=r.id and u.id=crd.companyid and r.gameid = :gameid order by r.roundNumber";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", game.getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setDecres_revenue((Double)row.get("revenue"));
				statBean.setDecres_expenses((Double)row.get("expenses"));
				if(row.get("newImage")!=null){
					statBean.setDecres_newImage((Double)row.get("newImage"));
				}
				statBean.setCompany_budget((Double)row.get("budget"));
				statBean.setDecres_marketingInvestment((Double)row.get("marketingInvestment"));
				statBean.setDecres_rdInvestment((Double)row.get("rdInvestment"));
				statBean.setDecres_employerEducationInvestment((Double)row.get("employerEducationInvestment"));
				statBean.setCompany_nickname((String)row.get("nickname"));
				statBean.setRound_number((Integer)row.get("roundNumber"));
				statBean.setDecres_overdraftCosts((Double)row.get("overdraftCosts"));
				statBean.setDecres_logisticCosts((Double)row.get("logisticCosts"));
				statBean.setDecres_newImage((Double)row.get("newImage"));
				statBean.setReport_name((String)row.get("reportTypeName"));
				statBean.setDecres_newBudget((Double)row.get("newBudget"));
				statBean.setDecres_reportCosts((Double)row.get("reportCosts"));
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Method to read all the relevant data for each round decision per company per game
	 * @param game
	 * @return
	 */
	public List<StatisticBean> getMarketGameSellingsData(Game game) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = " select u.nickname,r.roundNumber,sum(cp.amountSold) sell " +
					"from game g, user u, round r, COMPANYROUNDDECISIONANDRESULT crd, COMPANYPRODUCT cp "
					+ "where u.gameid=g.id and r.gameid=g.id and crd.roundId=r.id and crd.companyId=u.id and cp.decresid=crd.id and r.gameid = :gameid " +
					"group by r.roundNumber,g.name,u.nickname order by r.roundNumber asc";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", game.getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				statBean.setCompany_nickname((String)row.get("nickname"));
				statBean.setRound_number((Integer)row.get("roundNumber"));
				BigDecimal amount=(BigDecimal)row.get("sell");
				statBean.setAmountCompanyProducts(amount.toBigInteger());
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Method to read all the relevant CompanyProducts per company per game
	 * @param game
	 * @return
	 */
	public List<StatisticBean> getMarketGameCompanyProducts(Game game) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select p.productname,decr.newImage,s.supplierName,cp.sellingprice,p.productcosts,u.nickname,r.roundNumber,p.qualityFactor,p.imageFactor, cp.amountSold "+
						 "from COMPANYROUNDDECISIONANDRESULT decr, companyproduct cp,round r,user u,product p,supplier s "+
						 "where cp.decresId=decr.id and r.id=decr.roundId and decr.companyid=u.id and p.id=cp.productid and p.supplierid=s.id and r.gameId=:gameid";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", game.getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setProduct_name(((String)row.get("productname")));
				statBean.setCompprod_sellingPrice((Double)row.get("sellingprice"));
				statBean.setProduct_productCosts((Double)row.get("productcosts"));
				statBean.setCompany_nickname((String)row.get("nickname"));
				statBean.setRound_number((Integer)row.get("roundNumber"));
				statBean.setProduct_qualityFactor((Double)row.get("qualityFactor"));
				statBean.setProduct_imageFactor((Double)row.get("imageFactor"));
				statBean.setDecres_newImage((Double)row.get("newImage"));
				statBean.setSupplier_name((String)row.get("supplierName"));
				Integer amount=((Integer)row.get("amountSold"));
				statBean.setAmountCompanyProducts(BigInteger.valueOf(amount.intValue()));
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	
	/**
	 * Method to read all Products from the Database 
	 * @param game
	 * @return
	 */
	public List<StatisticBean> getMarketGameProducts() {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select s.supplierName,p.productName,p.qualityFactor,p.imageFactor,p.productCosts from supplier s,product p where p.supplierId=s.id";
			SQLQuery query = session.createSQLQuery(sql);
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setProduct_name(((String)row.get("productName")));
				statBean.setProduct_productCosts((Double)row.get("productCosts"));
				statBean.setProduct_qualityFactor((Double)row.get("qualityFactor"));
				statBean.setProduct_imageFactor((Double)row.get("imageFactor"));
				statBean.setSupplier_name((String)row.get("supplierName"));
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}

	
	
	/**
	 * Method to read all the relevant data for each round decision per company per game
	 * @param game
	 * @return
	 */
	public List<StatisticBean> getCompanyGameRoundDecisionData(Game game,User user) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select crd.revenue,crd.marketingInvestment,u.nickname,crd.employerEducationInvestment,r.roundNumber from COMPANYROUNDDECISIONANDRESULT crd,round r,user u where crd.roundid=r.id and u.id=crd.companyid and r.gameid = :gameid and u.id=:userid order by r.roundNumber";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", game.getId());
			query.setParameter("userid", user.getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setDecres_revenue((Double)row.get("revenue"));
				statBean.setDecres_marketingInvestment((Double)row.get("marketingInvestment"));
				statBean.setDecres_employerEducationInvestment((Double)row.get("employerEducationInvestment"));
				statBean.setCompany_nickname((String)row.get("nickname"));
				statBean.setRound_number((Integer)row.get("roundNumber"));
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Method to read the top selling companyproducts with their information per game
	 * @param game
	 * @return
	 */
	public List<StatisticBean> getAgentRoundInformation(Game game) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select at.agentName,a.preferencePrice,a.preferenceQuality,a.preferenceImage, p.productname,cp.sellingprice,"
				    +"r.roundNumber, s.supplierName, p.productCosts, u.nickname from "
					+ "agent a LEFT JOIN companyproduct cp ON (cp.id=a.companyproduct_id) " +
					"LEFT JOIN agenttype at ON (a.agenttype_id=at.id) " +
					"LEFT JOIN product p ON (cp.productid=p.id) " +
					"LEFT JOIN supplier s ON (p.supplierId=s.id) " +
					"LEFT JOIN companyrounddecisionandresult crd ON (cp.decresid=crd.id) " +
					"LEFT JOIN user u ON (u.id=crd.companyid), " +
					"round r where at.roundid=r.id and r.gameid= :gameid "
					+ "order by r.roundNumber asc";
			System.out.println(sql);
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", game.getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })  
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setRound_number(((Integer)row.get("roundNumber")));
				statBean.setProduct_productCosts((Double)row.get("productCosts"));
				statBean.setAgType_agentName(((String)row.get("agentName")));
				statBean.setCompprod_sellingPrice((Double)row.get("sellingprice"));
				statBean.setProduct_name(((String)row.get("productname")));
				statBean.setSupplier_name(((String)row.get("supplierName")));
				statBean.setCompany_nickname(((String)row.get("nickname")));
				statBean.setAgent_preferenceImage((Double)row.get("preferenceImage"));
				statBean.setAgent_preferencePrice((Double)row.get("preferencePrice"));
				statBean.setAgent_preferenceQuality((Double)row.get("preferenceQuality"));
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	public List<StatisticBean> getMarketGameAgentTypeSales(Game game){
		
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;		
		try {
			String sql = "Select p.productName, at.agentName, ac.amountSold as 'sumAmountSold' ,s.supplierName ,u.nickname, r.roundNumber "
					+ "from AGENTTYPE_COMPANYPRODUCT ac,agenttype at, companyproduct cp, product p, game g, round r, companyRoundDecisionAndResult crdr, supplier s , user u "
					+ "where p.id=cp.productId and cp.decresId=crdr.id and g.id=r.gameId and r.id=crdr.roundId and ac.companyproduct_id=cp.id and ac.agenttype_id=at.id "
					+ "and s.id=p.supplierId and u.id=crdr.companyId "
					+ "and g.id=:gameId";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameId", game.getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				statBean.setProduct_name((String)row.get("productName"));
				statBean.setCompprod_amountSold(BigInteger.valueOf(((Integer)row.get("sumAmountSold")).intValue()));
				statBean.setSupplier_name((String)row.get("supplierName"));
				statBean.setCompany_nickname((String)row.get("nickname"));
				statBean.setRound_number((Integer)row.get("roundNumber"));
				statBean.setAgent_name((String)row.get("agentName"));

				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}


	
	//-------------------------------Patrick-----------------------------------

	/**
	 * Get the whole amount of sold products of a company equal deadlineRound
	 * @param game
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameCompanySellings(Game game, Round deadlineRound) {

		List<StatisticBean> result = new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		// Transaction tx = null;
		try {
			// tx = session.beginTransaction();
			
			String sql = "Select u.nickname, sum(cp.amountSold) as 'sellings' "
							+ "from game g, user u, companyrounddecisionandresult crdr, companyProduct cp, round r "  
							+ "where g.id=u.gameId and u.id=crdr.companyId and crdr.id=cp.decresId and g.id=r.gameId and r.id=crdr.roundId and " 
							+ "u.admin=false and g.id=:gameId and r.roundNumber=:deadlineRound " 
							+ "group by u.nickname;";

			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameId", game.getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object, Object> row = (Map) object;
				StatisticBean statBean = new StatisticBean();
				
				statBean.setCompany_nickname((String) row.get("nickname"));
				BigDecimal anzahl = (BigDecimal) row.get("sellings");
				statBean.setCompprod_amountSold(BigInteger.valueOf(anzahl
						.intValue()));
				result.add(statBean);
			}
			// tx.commit();
		} catch (HibernateException e) {
			// if (tx != null)
			// tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}

		return result;
	}
	
	/**
	 * Get the amount of sold products of every round of a company before deadlineRound
	 * @param game
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameCompanySellingsPerRound(Game game, Round deadlineRound) {

		List<StatisticBean> result = new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		// Transaction tx = null;
		try {
			// tx = session.beginTransaction();
			
			String sql = "Select u.nickname, r.roundNumber, sum(cp.amountSold) as 'sellings' "
							+ "from game g, user u, companyrounddecisionandresult crdr, companyProduct cp, "
							+ "round r "
							+ "where g.id=u.gameId and u.id=crdr.companyId and crdr.id=cp.decresId and " 
							+ "u.admin=false and g.id=:gameId and g.id=r.gameId and r.id=crdr.roundId and r.roundNumber<=:deadlineRound " 
							+ "group by u.nickname,r.roundNumber;";

			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameId", game.getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object, Object> row = (Map) object;
				StatisticBean statBean = new StatisticBean();
				
				statBean.setCompany_nickname((String) row.get("nickname"));
				statBean.setRound_number((Integer)row.get("roundNumber"));
				BigDecimal anzahl = (BigDecimal) row.get("sellings");
				statBean.setCompprod_amountSold(BigInteger.valueOf(anzahl
						.intValue()));
				result.add(statBean);
			}
			// tx.commit();
		} catch (HibernateException e) {
			// if (tx != null)
			// tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}

		return result;
	}
	
	
	/**
	 * Get all company investments (personal, rd, marketing)
	 * @param company
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getCompanyGameInvestments(User company){
		List<StatisticBean> data = new ArrayList<StatisticBean>();
		StatisticBean sb=null;
		for(CompanyRoundDecisionAndResult decres:company.getCompanyRoundDecisionAndResults()){
			sb = new StatisticBean();
			sb.setRound_number(decres.getRound().getRoundNumber());
			sb.setDecres_marketingInvestment(decres.getMarketingInvestment());
			sb.setDecres_employerEducationInvestment(decres.getEmployerEducationInvestment());
			sb.setDecres_rdInvestment(decres.getRdInvestment());
			data.add(sb);
		}
		return data;
	}
	
	/**
	 * Get companyProducts per Round of different companies equal deadline
	 * @param game
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameCompanyProducts(Game game,Round deadlineRound){
		
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select u.nickname,r.roundNumber, p.productName, s.supplierName, p.qualityFactor, p.productCosts, cp.sellingPrice, cp.amountSold " 
					+ "from COMPANYROUNDDECISIONANDRESULT crd,round r,user u, "
					+ "companyproduct cp, product p, supplier s " 
					+ "where crd.roundid=r.id and u.id=crd.companyid "
					+ "and cp.decresId=crd.id and p.id=cp.productId and s.id=p.supplierId " 
					+ "and r.roundNumber = :deadlineRound and r.gameid = :gameid " 
					+ "order by r.roundNumber";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", game.getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				statBean.setCompany_nickname((String)row.get("nickname"));
				statBean.setRound_number((Integer)row.get("roundNumber"));
				statBean.setProduct_name((String)row.get("productName"));
				statBean.setSupplier_name((String)row.get("supplierName"));
				statBean.setProduct_qualityFactor((Double)row.get("qualityFactor"));
				statBean.setProduct_productCosts((Double)row.get("productCosts"));
				statBean.setCompprod_sellingPrice((Double)row.get("sellingPrice"));
				statBean.setCompprod_amountSold(BigInteger.valueOf(((Integer)row.get("amountSold")).intValue()));

				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
		
	}
	
	/**
	 * Get different products with sellings equal deadlineRound
	 * @param game
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameProductSellings(Game game,Round deadlineRound){
		
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			// tx = session.beginTransaction();
			String sql = "Select p.id, p.productName, sum(cp.amountSold) as 'sumAmountSold' "
					+ "from companyproduct cp, product p, game g, round r, companyRoundDecisionAndResult crdr "
					+ "where p.id=cp.productId and cp.decresId=crdr.id and g.id=r.gameId and r.id=crdr.roundId "
					+ "and g.id=:gameId and r.roundNumber=:deadlineRound "
					+ "group by p.id, p.productName;";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameId", game.getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				statBean.setProduct_name((String)row.get("productName"));
				statBean.setCompprod_amountSold(BigInteger.valueOf(((BigDecimal)row.get("sumAmountSold")).intValue()));

				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
		
	}
	

	/**
	 * Get number of agents with specific preferenceQuality equal deadlineRound
	 * @param game
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameAgentQuality(Game game, Round deadlineRound) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "Select round(a.preferenceQuality*10) as 'prefQual',count(*) as 'anz' "
							+ "from round r, agentType at, agent a "
							+ "where r.id=at.roundId and at.id=a.agenttype_id and " 
							+ "r.gameId=:gameId and r.roundNumber=:deadlineRound "
							+ "group by round(a.preferenceQuality*10);";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameId", game.getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				statBean.setAgent_preferenceQuality((Double)row.get("prefQual"));
				statBean.setCount(((BigInteger)row.get("anz")));

				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Get number of agents with specific preferenceQuality equal deadlineRound
	 * @param game
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameAgentPrice(Game game, Round deadlineRound) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "Select round(a.preferencePrice*10) as 'prefPrice',count(*) as 'anz' "
							+ "from round r, agentType at, agent a "
							+ "where r.id=at.roundId and at.id=a.agenttype_id and " 
							+ "r.gameId=:gameId and r.roundNumber=:deadlineRound "
							+ "group by round(a.preferencePrice*10);";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameId", game.getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				statBean.setAgent_preferencePrice((Double)row.get("prefPrice"));
				statBean.setCount(((BigInteger)row.get("anz")));

				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Get number of agents with specific preferenceQuality equal deadlineRound
	 * @param game
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameAgentImage(Game game, Round deadlineRound) {
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "Select round(a.preferenceImage*10) as 'prefImage',count(*) as 'anz' "
							+ "from round r, agentType at, agent a "
							+ "where r.id=at.roundId and at.id=a.agenttype_id and " 
							+ "r.gameId=:gameId and r.roundNumber=:deadlineRound "
							+ "group by round(a.preferenceImage*10);";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameId", game.getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				statBean.setAgent_preferenceImage((Double)row.get("prefImage"));
				statBean.setCount(((BigInteger)row.get("anz")));

				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Get amount of sold products for a specific round for the companies
	 * @param game
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketLastRoundSellings(Game game, Round deadlineRound) {

		List<StatisticBean> result = new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		// Transaction tx = null;
		try {
			// tx = session.beginTransaction();
			Round round = deadlineRound;
			String sql = "select usr.nickname, sum(cp.amountSold) as 'anz' from CompanyRoundDecisionAndResult dr, "
					+ "companyproduct cp, user usr, round r "
					+ "where dr.roundid=:roundId and r.id=dr.roundId and r.gameId=:gameId and dr.id=cp.decresid and dr.companyId=usr.id " 
					+ "and usr.admin=false group by dr.companyId";

			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("roundId", round.getId());
			query.setParameter("gameId", round.getGame().getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object, Object> row = (Map) object;
				StatisticBean statBean = new StatisticBean();

				BigDecimal anzahl = (BigDecimal) row.get("anz");

				statBean.setCompprod_amountSold(BigInteger.valueOf(anzahl
						.intValue()));
				statBean.setCompany_nickname((String) row.get("nickname"));
				result.add(statBean);
			}
			// tx.commit();
		} catch (HibernateException e) {
			// if (tx != null)
			// tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}

		return result;
	}
	
	/**
	 * Returns the previous (before deadline) decisions of a specific company
	 * @param competitor
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getCompanyGameDecisions(User competitor, Round deadlineRound, boolean exactRound) {

		List<StatisticBean> result=new ArrayList<StatisticBean>();
		
		if(deadlineRound==null){
			return result;
		}
		
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select u.nickname,crd.revenue,crd.marketingInvestment,crd.employerEducationInvestment,crd.rdInvestment,r.roundNumber,crd.expenses,crd.newImage,crd.overdraftCosts,crd.logisticCosts,crd.reportCosts,crd.newBudget " 
					+ "from COMPANYROUNDDECISIONANDRESULT crd,round r,user u " 
					+ "where crd.roundid=r.id and u.id=crd.companyid and r.gameid = :gameid " 
					+ "and u.id=:userid and " ;
					
			if(exactRound){
				sql = sql + "r.roundNumber = :deadlineRound " 
						+ "order by r.roundNumber"; 
			}else{
				sql = sql + "r.roundNumber <= :deadlineRound " 
						+ "order by r.roundNumber"; 
	
			}
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", deadlineRound.getGame().getId());
			query.setParameter("userid", competitor.getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setDecres_revenue((Double)row.get("revenue"));
				statBean.setDecres_expenses((Double)row.get("expenses"));
				statBean.setDecres_marketingInvestment((Double)row.get("marketingInvestment"));
				statBean.setDecres_employerEducationInvestment((Double)row.get("employerEducationInvestment"));
				statBean.setDecres_rdInvestment((Double)row.get("rdInvestment"));
				statBean.setCompany_nickname((String)row.get("nickname"));
				statBean.setRound_number((Integer)row.get("roundNumber"));
				statBean.setDecres_newImage((double)row.get("newImage"));
				
				if(row.get("newBudget")!=null){
					statBean.setDecres_newBudget((double)row.get("newBudget"));
				}
				if(row.get("overdraftCosts")!=null){
					statBean.setDecres_overdraftCosts((double)row.get("overdraftCosts"));
				}
				if(row.get("logisticCosts")!=null){
					statBean.setDecres_logisticCosts((double)row.get("logisticCosts"));
				}
				if(row.get("reportCosts")!=null){
					statBean.setDecres_reportCosts((double)row.get("reportCosts"));
				}
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Returns the previous (equal deadline) decisions for all companies
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameDecisionsDeadline(Round deadlineRound) {

		List<StatisticBean> result=new ArrayList<StatisticBean>();
		
		if(deadlineRound==null){
			return result;
		}
		
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select u.nickname,r.roundNumber,crd.newImage, crd.revenue as 'rev',crd.expenses as 'exp', crd.newBudget, " 
					+ "crd.marketingInvestment as 'markInvs',crd.employerEducationInvestment as 'persInvs',crd.rdInvestment as 'rdInvs' "
					+ "from COMPANYROUNDDECISIONANDRESULT crd,round r,user u "
					+ "where crd.roundid=r.id and u.id=crd.companyid and r.gameid = :gameid " 
					+ "and r.roundNumber = :deadlineRound " 
					+ "order by r.roundNumber;";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", deadlineRound.getGame().getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setCompany_nickname((String)row.get("nickname"));
				statBean.setRound_number((Integer)row.get("roundNumber"));
				statBean.setDecres_newImage((Double)row.get("newImage"));
				
				if(row.get("newBudget")!=null){
					statBean.setDecres_newBudget((Double)row.get("newBudget"));
				}
				
				//revenue
				Double sum = (Double) row.get("rev");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_revenue(sum);
				
				//expenses
				sum = (Double) row.get("exp");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_expenses(sum);
				
				//marketingInvestment
				sum = (Double) row.get("markInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_marketingInvestment(sum);
				
				//personalInvestment
				sum = (Double) row.get("persInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_employerEducationInvestment(sum);
				
				//RD-Investment
				sum = (Double) row.get("rdInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_rdInvestment(sum);	
				
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Returns the previous (equal deadline) decisions for all companies
	 * @param deadlineRound
	 * @param percentage
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameDecisionsDeadlinePercentage(Round deadlineRound, double percentage, User company) {

		List<StatisticBean> result=new ArrayList<StatisticBean>();
		int companyAmount = 0;
		long pct = 0;
		
		if(deadlineRound==null){
			return result;
		}
		
		if(deadlineRound.getCompanyRoundDecisionAndResults()!=null){
			companyAmount = deadlineRound.getCompanyRoundDecisionAndResults().size()-1;
		}
		
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select usr.nickname,usr.roundNumber,usr.newImage, usr.revenue as 'rev',usr.expenses as 'exp', " 
					+ "usr.marketingInvestment as 'markInvs',usr.employerEducationInvestment as 'persInvs',usr.rdInvestment as 'rdInvs',usr.newBudget "
					+ "from (Select crd.newImage, crd.marketingInvestment, crd.employerEducationInvestment, crd.rdInvestment,crd.newBudget, " 
					+ "crd.revenue, crd.expenses, r.roundNumber, u.nickname from COMPANYROUNDDECISIONANDRESULT crd,round r, " 
					+ "(Select * from user u1 where u1.id<>:myId) u " //(Select * from user u1 where u1.id<>:myId) user u
					+ "where crd.roundid=r.id and u.id=crd.companyid and r.gameid = :gameid " 
					+ "and r.roundNumber = :deadlineRound " 
					+ "order by crd.newImage desc limit :pct) as usr "
					+ "order by usr.roundNumber;";
			
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", deadlineRound.getGame().getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			pct = Math.round(companyAmount*percentage/100);
			query.setParameter("pct", pct);
			query.setParameter("myId", company.getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setCompany_nickname((String)row.get("nickname"));
				statBean.setRound_number((Integer)row.get("roundNumber"));
				statBean.setDecres_newImage((Double)row.get("newImage"));
				
				if(row.get("newBudget")!=null){
					statBean.setDecres_newBudget((Double)row.get("newBudget"));
				}
				
				//revenue
				Double sum = (Double) row.get("rev");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_revenue(sum);
				
				//expenses
				sum = (Double) row.get("exp");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_expenses(sum);
				
				//marketingInvestment
				sum = (Double) row.get("markInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_marketingInvestment(sum);
				
				//personalInvestment
				sum = (Double) row.get("persInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_employerEducationInvestment(sum);
				
				//RD-Investment
				sum = (Double) row.get("rdInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_rdInvestment(sum);	
				
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Returns the previous (equal deadline) summed up decisions for all companies
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameSummedUpDecisionsDeadline(Round deadlineRound) {

		List<StatisticBean> result=new ArrayList<StatisticBean>();
		
		if(deadlineRound==null){
			return result;
		}
		
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select sum(crd.revenue) as 'rev',sum(crd.expenses) as 'exp',sum(crd.marketingInvestment) as 'markInvs',sum(crd.employerEducationInvestment) as 'persInvs',sum(crd.rdInvestment) as 'rdInvs' " 
					+ "from COMPANYROUNDDECISIONANDRESULT crd,round r " 
					+ "where crd.roundid=r.id and r.gameid = :gameid " 
					+ "and r.roundNumber = :deadlineRound "
					+ "order by r.roundNumber;";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", deadlineRound.getGame().getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				statBean.setCompany_nickname((String)row.get("nickname"));
				
				//revenue
				Double sum = (Double) row.get("rev");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_revenue(sum);
				
				//expenses
				sum = (Double) row.get("exp");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_expenses(sum);
				
				//marketingInvestment
				sum = (Double) row.get("markInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_marketingInvestment(sum);
				
				//personalInvestment
				sum = (Double) row.get("persInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_employerEducationInvestment(sum);
				
				//RD-Investment
				sum = (Double) row.get("rdInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_rdInvestment(sum);	
				
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Returns the previous (= round) decisions for all companies summed up
	 * @param round
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameDecisionsAtRound(Round round) {

		List<StatisticBean> result=new ArrayList<StatisticBean>();
		
		if(round==null){
			return result;
		}
		
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select sum(crd.revenue) as 'rev',sum(crd.marketingInvestment) as 'markInvs',sum(crd.employerEducationInvestment) as 'persInvs',sum(crd.rdInvestment) as 'rdInvs' " 
					+ "from COMPANYROUNDDECISIONANDRESULT crd,round r,user u " 
					+ "where crd.roundid=r.id and u.id=crd.companyid and r.gameid = :gameid " 
					+ "and r.roundNumber = :specificRound ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", round.getGame().getId());
			query.setParameter("specificRound", round.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				//revenue
				Double sum = (Double) row.get("rev");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_revenue(sum);
				
				//marketingInvestment
				sum = (Double) row.get("markInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_marketingInvestment(sum);
				
				//personalInvestment
				sum = (Double) row.get("persInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_employerEducationInvestment(sum);
				
				//RD-Investment
				sum = (Double) row.get("rdInvs");
				if(sum==null){
					sum = 0.0;
				}
				statBean.setDecres_rdInvestment(sum);	
				
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Returns the previous (before deadline) decisions of a specific company
	 * @param competitor
	 * @param deadlineRound
	 * @param exactRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getCompanyGameCompanyProducts(User competitor, Round deadlineRound, boolean exactRound) {

		List<StatisticBean> result=new ArrayList<StatisticBean>();
		if(deadlineRound==null){
			return result;
		}
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "select u.nickname,r.roundNumber, p.productName, s.supplierName, p.qualityFactor, p.productCosts, cp.sellingPrice, cp.amountSold " 
					+ "from COMPANYROUNDDECISIONANDRESULT crd,round r,user u, "
					+ "companyproduct cp, product p, supplier s " 
					+ "where crd.roundid=r.id and u.id=crd.companyid "
					+ "and cp.decresId=crd.id and p.id=cp.productId and s.id=p.supplierId " 
					+ "and u.id=:userid and r.gameid = :gameid and " ;
			
			if(exactRound){
				sql = sql + "r.roundNumber = :deadlineRound " 
						+ "order by r.roundNumber";
			}else{
				sql = sql + "r.roundNumber <= :deadlineRound " 
						+ "order by r.roundNumber";
			}
					
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", deadlineRound.getGame().getId());
			query.setParameter("userid", competitor.getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				statBean.setCompany_nickname((String)row.get("nickname"));
				statBean.setRound_number((Integer)row.get("roundNumber"));
				statBean.setProduct_name((String)row.get("productName"));
				statBean.setSupplier_name((String)row.get("supplierName"));
				statBean.setProduct_qualityFactor((Double)row.get("qualityFactor"));
				statBean.setProduct_productCosts((Double)row.get("productCosts"));
				statBean.setCompprod_sellingPrice((Double)row.get("sellingPrice"));
				statBean.setCompprod_amountSold(BigInteger.valueOf(((Integer)row.get("amountSold")).intValue()));

				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Get the different products equal deadlineRound with sellings (amountSold)
	 * @param game
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketLastRoundsProducts(Game game, Round deadlineRound) {

		List<StatisticBean> result = new ArrayList<StatisticBean>();
		Session session = sessionFactory.openSession();
		// Transaction tx = null;
		try {
			// tx = session.beginTransaction();
			Round round = deadlineRound;
			String sql = "select prod.productName, prod.qualityFactor, prod.productCosts,cp.sellingPrice, cp.amountSold " + 
					"from CompanyRoundDecisionAndResult dr, companyproduct cp, product prod, Round r "
					+ "where dr.roundid=:roundId and r.id=dr.roundid and r.gameid=:gameId and dr.id=cp.decresid and cp.productId=prod.id";

			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("roundId", round.getId());
			query.setParameter("gameId", deadlineRound.getGame().getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object, Object> row = (Map) object;
				StatisticBean statBean = new StatisticBean();

				statBean.setProduct_name((String) row.get("productName"));
				statBean.setProduct_qualityFactor((Double)row.get("qualityFactor"));
				statBean.setProduct_productCosts((Double)row.get("productCosts"));
				statBean.setCompprod_sellingPrice((Double)row.get("sellingPrice"));
				Integer anzahl = (Integer) row.get("amountSold");
				statBean.setCompprod_amountSold(BigInteger.valueOf(anzahl.intValue()));
				
				result.add(statBean);
			}
			// tx.commit();
		} catch (HibernateException e) {
			// if (tx != null)
			// tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}

		return result;
	}
	
	/**
	 * Get the average preferences of a specific amount (%) of agents
	 * = deadline round
	 * @param round
	 * @param percentage
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameAgentPreferencesPercentage(Round round, double percentage) {

		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Integer agentAmount = null;
		long pct;
		
		if(round==null){
			return result;
		}
		
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			
			agentAmount = this.getMarketGameAgentAmount(round.getGame(), round);
			
			//tx = session.beginTransaction();
			String sql = "Select round(avg(ag.preferenceQuality),3) as prefQuality, round(avg(ag.preferencePrice),3) as prefPrice,round(avg(ag.preferenceImage),3) as prefImage " 
					+ "from (Select a.* from agent a, round r,agentType at " 
					+ "where r.gameid = :gameid and r.roundNumber = :specificRound and r.id=at.roundId and at.id=a.agenttype_id ";
			
			if(percentage==33){
				sql = sql + "and (a.id % 3 = 0) ";
			}else if(percentage==66){
				sql = sql + "and (a.id % 3 = 0) and (a.id % 2 = 0) ";
			}
				
			//sql = sql + "order by rand() limit :pct) as ag ";
			sql = sql + "limit :pct) as ag ";
			
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", round.getGame().getId());
			query.setParameter("specificRound", round.getRoundNumber());
			pct = Math.round(agentAmount*percentage/100);
			query.setParameter("pct", pct);
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				statBean.setAgent_preferenceQuality((Double)row.get("prefQuality"));	
				statBean.setAgent_preferencePrice((Double)row.get("prefPrice"));
				statBean.setAgent_preferenceImage((Double)row.get("prefImage"));
				
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Get a specific amount (%) of agents with preferences
	 * = deadlineRound
	 * @param round
	 * @param percentage
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameAgentPercentage(Round round, double percentage) {

		List<StatisticBean> result=new ArrayList<StatisticBean>();
		Integer agentAmount = null;
		long pct;
		
		if(round==null){
			return result;
		}
		
		Session session = sessionFactory.openSession();
		//Transaction tx = null;
		try {
			
			agentAmount = this.getMarketGameAgentAmount(round.getGame(), round);
			
			//tx = session.beginTransaction();
			String sql = "Select ag.id as agID, ag.preferenceQuality as prefQuality,ag.preferencePrice as prefPrice,ag.preferenceImage as prefImage " 
					+ ", p.productName as pName "
					+ "from (Select a.* from agent a, agentType at, round r where r.gameid = :gameid and r.roundNumber = :specificRound and r.id=at.roundId and at.id=a.agenttype_id " 
					+ "order by rand() limit :pct) as ag " 
					+ "LEFT JOIN companyProduct cp ON (ag.companyproduct_id=cp.id) " 
					+ "LEFT JOIN product p ON (cp.productid=p.id) ";
			
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameid", round.getGame().getId());
			query.setParameter("specificRound", round.getRoundNumber());
			pct = Math.round(agentAmount*percentage/100);
			query.setParameter("pct", pct);
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				statBean.setAgent_preferenceQuality((Double)row.get("prefQuality"));	
				statBean.setAgent_preferencePrice((Double)row.get("prefPrice"));
				statBean.setAgent_preferenceImage((Double)row.get("prefImage"));
				statBean.setProduct_name((String)row.get("pName"));
				
				result.add(statBean);
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
	}
	
	/**
	 * Returns the amount of agents equal deadlineRound
	 * @param game
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public Integer getMarketGameAgentAmount(Game game, Round deadlineRound) {
		Session session = sessionFactory.openSession();
		BigInteger agentAmount=null;
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "Select count(*) as 'anz' "
							+ "from round r, agentType at, agent a "
							+ "where r.id=at.roundId and at.id=a.agenttype_id and " 
							+ "r.gameId=:gameId and r.roundNumber=:deadlineRound ;";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameId", game.getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				
				agentAmount = (BigInteger)row.get("anz");
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		if(agentAmount==null){
			return null;
		}else{
			return agentAmount.intValue();
		}
	}
	
	/**
	 * Returns the amount of companies before/equal deadlineRound
	 * @param game
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public Integer getMarketGameCompanyAmount(Game game) {
		Session session = sessionFactory.openSession();
		BigInteger companyAmount=null;
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "Select count(*) as 'anz' "
							+ "from game g, user u "
							+ "where g.id=u.gameId and g.id=:gameId ;";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameId", game.getId());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				
				companyAmount = (BigInteger)row.get("anz");
			}
			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		if(companyAmount==null){
			return null;
		}else{
			return companyAmount.intValue();
		}
	}
	
	/**
	 * Returns the amount of different values equal deadlineRound
	 * @param deadlineRound
	 * @author Patrick
	 * @return
	 */
	public List<StatisticBean> getMarketGameAmounts(Round deadlineRound) {
		Session session = sessionFactory.openSession();
		List<StatisticBean> result=new ArrayList<StatisticBean>();
		//Transaction tx = null;
		try {
			//tx = session.beginTransaction();
			String sql = "Select count(*) as 'allCP', count(distinct cp.productID) as 'diffP' "
							+ "from round r, companyrounddecisionandResult decres, companyProduct cp "
							+ "where r.id=decres.roundId and decres.id=cp.decresId " 
							+ "and r.gameId=:gameId and r.roundNumber=:deadlineRound ;";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameter("gameId", deadlineRound.getGame().getId());
			query.setParameter("deadlineRound", deadlineRound.getRoundNumber());
			query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
			
			
			@SuppressWarnings("unchecked")
			List<Object> data = query.list();

			for (Object object : data) {
				@SuppressWarnings({ "unchecked", "rawtypes" })
				Map<Object,Object> row = (Map) object;
				StatisticBean statBean=new StatisticBean();
				
				statBean.setAmountCompanyProducts((BigInteger)row.get("allCP"));
				statBean.setAmountDifferentProducts((BigInteger)row.get("diffP"));
				
				statBean.setAgent_preferenceQuality((Double)row.get("prefQuality"));	
				statBean.setAgent_preferencePrice((Double)row.get("prefPrice"));
				statBean.setAgent_preferenceImage((Double)row.get("prefImage"));
				statBean.setProduct_name((String)row.get("pName"));
				
				result.add(statBean);
			}

			//tx.commit();
		} catch (HibernateException e) {
			//if (tx != null)
				//tx.rollback();
			e.printStackTrace();
		} finally {
			session.close();
		}
		
		return result;
		
	}
}
